﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DreamBuilder.DictionaryAccess;


namespace DreamBuilder.UI.UserControls.DataAccess.Services
{
    public abstract class UCControlCommon : UserControlsDataAccessBase
    {

        /// <summary>
        /// 获取参照的fromSql，selectSql
        /// </summary>
        /// <param name="pFieldDicInfo"></param>
        /// <param name="fromSql"></param>
        /// <param name="selectSql"></param>
        public static void GetRefSelectAndFromSql(FieldDicInfo pFieldDicInfo, ref string fromSql, ref string selectSql)
        {
            string sTableCode = pFieldDicInfo.TableCode.Trim();
            string sFieldCode = pFieldDicInfo.FieldCode.Trim();


            string referenceObj = pFieldDicInfo.ReferenceObj.Trim();
            if (string.IsNullOrEmpty(referenceObj))
            {
                throw new Exception("TableCode:" + pFieldDicInfo.TableCode + " and FieldCode:" + pFieldDicInfo.FieldCode + "'s ReferenceObj is null in Configuration_Sys_Field");
            }


            string sJoinTable = pFieldDicInfo.TableCode;
            string sFKField = pFieldDicInfo.FieldCode;

            TableDicInfo pRefTableDicInfo = DictionaryAccessFacade.GetTableDicInfo(referenceObj);
            string sRefDisplayField = "";
            string sRefTableCode = "";
            int i = 0;
            while (pRefTableDicInfo != null)
            {
                /// 读取该表的显示字段
                if (pRefTableDicInfo.DisplayField != null)
                {
                    sRefDisplayField = pRefTableDicInfo.DisplayField.Trim();
                    sRefTableCode = pRefTableDicInfo.TableCode.Trim();
                    i = 0;
                    if (sRefDisplayField == pFieldDicInfo.FieldCode && sRefTableCode == pFieldDicInfo.TableCode)
                    {
                        //throw new Exception("TableCode:" + pFieldDicInfo.TableCode + " and FieldCode:" + pFieldDicInfo.FieldCode + "'s ReferenceObj can't be self table");
                        break;
                    }
                    //vTableConnectSql or vMainTable
                    if (fromSql.ToLower().IndexOf(" join " + pRefTableDicInfo.TableCode.ToLower() + " on") > 0)
                    {
                        i++;
                    }
                    //参照的表是第一个 == 0 
                    if (fromSql.ToLower().IndexOf(pRefTableDicInfo.TableCode.ToLower() + " left join") == 0)
                    {
                        i++;
                    }
                    //如果i递增后，仍然在from语句中存在此参照表，则不断循环递增
                    while (fromSql.ToLower().IndexOf(pRefTableDicInfo.TableCode.ToLower() + "_" + i.ToString() + " on") > 0)
                    {
                        i++;
                    }
                    //如果from语句和表连接串中都不存在此表，则判断单据的表是否和参照表相同，相同，则递增
                    if (i == 0 && fromSql.ToLower().Trim() == pRefTableDicInfo.TableCode.ToLower().Trim())
                    {
                        i++;
                    }
                    if (i == 0)
                    {
                        fromSql = fromSql + " left join " + pRefTableDicInfo.TableCode + " on " + sJoinTable.ToString().Trim() + "." + sFKField.ToString().Trim() + " = " + pRefTableDicInfo.TableCode + "." + pRefTableDicInfo.PrimaryField;
                        sJoinTable = pRefTableDicInfo.TableCode;
                        sFKField = pRefTableDicInfo.DisplayField;
                    }
                    else
                    {
                        fromSql = fromSql + " left join " + pRefTableDicInfo.TableCode + "  " + pRefTableDicInfo.TableCode + "_" + i.ToString() + " on " + sJoinTable.ToString().Trim() + "." + sFKField.ToString().Trim() + " = " + pRefTableDicInfo.TableCode + "_" + i.ToString() + "." + pRefTableDicInfo.PrimaryField;
                        sJoinTable = pRefTableDicInfo.TableCode + "_" + i.ToString();
                        sFKField = pRefTableDicInfo.DisplayField;
                    }
                    //寻找看DisplayField是否为多次参照
                    pFieldDicInfo = DictionaryAccessFacade.GetFieldDicInfo(pRefTableDicInfo.TableCode.Trim(), pRefTableDicInfo.DisplayField.Trim());
                    if (string.IsNullOrEmpty(pFieldDicInfo.ReferenceObj))
                    {
                        pRefTableDicInfo = null;
                    }
                    else
                    {
                        pRefTableDicInfo = DictionaryAccessFacade.GetTableDicInfo(pFieldDicInfo.ReferenceObj);
                    }
                }
            }
            //给SelectSql赋值
            if (i == 0)
            {
                selectSql += sRefTableCode + "." + sRefDisplayField + " as " + sTableCode + "__" + sFieldCode + ",";
            }
            else
            {
                selectSql += sRefTableCode + "_" + i.ToString() + "." + sRefDisplayField + " as " + sTableCode + "__" + sFieldCode + ",";
            }
        }


        /// <summary>
        /// 获取参照的fromSql，selectSql
        /// </summary>
        /// <param name="pFieldDicInfo"></param>
        /// <param name="fromSql"></param>
        /// <param name="selectSql"></param>
        public static void GetRefSelectAndFromSql(FieldDicInfo pFieldDicInfo, ref string fromSql, ref string selectSql,int columnIndex)
        {
            string sTableCode = pFieldDicInfo.TableCode.Trim();
            string sFieldCode = pFieldDicInfo.FieldCode.Trim();


            string referenceObj = pFieldDicInfo.ReferenceObj.Trim();
            if (string.IsNullOrEmpty(referenceObj))
            {
                throw new Exception("TableCode:" + pFieldDicInfo.TableCode + " and FieldCode:" + pFieldDicInfo.FieldCode + "'s ReferenceObj is null in Configuration_Sys_Field");
            }


            string sJoinTable = pFieldDicInfo.TableCode;
            string sFKField = pFieldDicInfo.FieldCode;

            TableDicInfo pRefTableDicInfo = DictionaryAccessFacade.GetTableDicInfo(referenceObj);
            string sRefDisplayField = "";
            string sRefTableCode = "";
            int i = 0;
            while (pRefTableDicInfo != null)
            {
                /// 读取该表的显示字段
                if (pRefTableDicInfo.DisplayField != null)
                {
                    sRefDisplayField = pRefTableDicInfo.DisplayField.Trim();
                    sRefTableCode = pRefTableDicInfo.TableCode.Trim();
                    i = 0;
                    if (sRefDisplayField == pFieldDicInfo.FieldCode && sRefTableCode == pFieldDicInfo.TableCode)
                    {
                        throw new Exception("TableCode:" + pFieldDicInfo.TableCode + " and FieldCode:" + pFieldDicInfo.FieldCode + "'s ReferenceObj can't be self table");
                    }
                    //vTableConnectSql or vMainTable
                    if (fromSql.ToLower().IndexOf(" join " + pRefTableDicInfo.TableCode.ToLower() + " on") > 0)
                    {
                        i++;
                    }
                    //参照的表是第一个 == 0 
                    if (fromSql.ToLower().IndexOf(pRefTableDicInfo.TableCode.ToLower() + " left join") == 0)
                    {
                        i++;
                    }
                    //如果i递增后，仍然在from语句中存在此参照表，则不断循环递增
                    while (fromSql.ToLower().IndexOf(pRefTableDicInfo.TableCode.ToLower() + "_" + i.ToString() + " on") > 0)
                    {
                        i++;
                    }
                    //如果from语句和表连接串中都不存在此表，则判断单据的表是否和参照表相同，相同，则递增
                    if (i == 0 && fromSql.ToLower().Trim() == pRefTableDicInfo.TableCode.ToLower().Trim())
                    {
                        i++;
                    }
                    if (i == 0)
                    {
                        fromSql = fromSql + " left join " + pRefTableDicInfo.TableCode + " on " + sJoinTable.ToString().Trim() + "." + sFKField.ToString().Trim() + " = " + pRefTableDicInfo.TableCode + "." + pRefTableDicInfo.PrimaryField;
                        sJoinTable = pRefTableDicInfo.TableCode;
                        sFKField = pRefTableDicInfo.DisplayField;
                    }
                    else
                    {
                        fromSql = fromSql + " left join " + pRefTableDicInfo.TableCode + "  " + pRefTableDicInfo.TableCode + "_" + i.ToString() + " on " + sJoinTable.ToString().Trim() + "." + sFKField.ToString().Trim() + " = " + pRefTableDicInfo.TableCode + "_" + i.ToString() + "." + pRefTableDicInfo.PrimaryField;
                        sJoinTable = pRefTableDicInfo.TableCode + "_" + i.ToString();
                        sFKField = pRefTableDicInfo.DisplayField;
                    }
                    //寻找看DisplayField是否为多次参照
                    pFieldDicInfo = DictionaryAccessFacade.GetFieldDicInfo(pRefTableDicInfo.TableCode.Trim(), pRefTableDicInfo.DisplayField.Trim());
                    if (string.IsNullOrEmpty(pFieldDicInfo.ReferenceObj))
                    {
                        pRefTableDicInfo = null;
                    }
                    else
                    {
                        pRefTableDicInfo = DictionaryAccessFacade.GetTableDicInfo(pFieldDicInfo.ReferenceObj);
                    }
                }
            }
            //给SelectSql赋值
            if (i == 0)
            {
                selectSql += sRefTableCode + "." + sRefDisplayField + " as COLUMN" + columnIndex + ",";
            }
            else
            {
                selectSql += sRefTableCode + "_" + i.ToString() + "." + sRefDisplayField + " as COLUMN" + columnIndex + ",";
            }
        }


        /// <summary>
        /// 根据参照的表名，参照值获取显示的参照信息
        /// </summary>
        /// <param name="refTableCode">参照的表名</param>
        /// <param name="refCodeValue">参照值</param>
        /// <returns></returns>
        public IDataReader GetRefTextReader(string refTableCode, string refCodeValue)
        {
            TableDicInfo pTableDicInfo = DictionaryAccessFacade.GetTableDicInfo(refTableCode);
            string sPK = pTableDicInfo.PrimaryField;
            string sDisplayField = pTableDicInfo.DisplayField;
            string sql = " select " + sDisplayField + " from " + refTableCode + " where " + sPK + " in ";
            FieldDicInfo fieldDicInfo = DictionaryAccessFacade.GetFieldDicInfo(pTableDicInfo.TableCode, pTableDicInfo.PrimaryField);
            if (fieldDicInfo != null)
            {
                if (fieldDicInfo.FieldType == FIELDTYPE.TYPE_Integer ||
                    fieldDicInfo.FieldType == FIELDTYPE.TYPE_Decimal ||
                    fieldDicInfo.FieldType == FIELDTYPE.TYPE_Bool
                    )
                {
                    sql += "(" + refCodeValue + ")";
                }
                else
                {
                    sql += "('" + refCodeValue.Replace(",", "'',''") + "')";
                }
            }
            return DBHelper.ExecuteReader(sql);
        }

        ///<summary>
        ///根据表名，获取显示的信息
        ///</summary>
        /// <param name="refTableCode">表名</param>
        /// <param name="refCodeValue">条件</param>
        /// <returns></returns>
        public IDataReader GetBindData(string tablecode, string whereSql)
        {
            TableDicInfo pTableDicInfo = DictionaryAccessFacade.GetTableDicInfo(tablecode);
            string primaryField = pTableDicInfo.PrimaryField;
            string displayField = pTableDicInfo.DisplayField;

            string sql = "select " + primaryField + "," + displayField + " from " + tablecode;

            if (!string.IsNullOrEmpty(whereSql))
            {
                sql += " where " + whereSql;
            }
            if (!string.IsNullOrEmpty(pTableDicInfo.OrderSql))
            {
                sql += " order by " + tablecode + "." + pTableDicInfo.OrderSql;
            }
            return DBHelper.ExecuteReader(sql);
        }
    }
}
